set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
insert overwrite table jms_dm.dm_waybill_collect_intime_summary_dt
select
     main.network_code                as network_code                --交件网点编码
    ,main.network_name                as network_name                --交件网点名称
    ,main.network_fran_code           as network_fran_code           --交件网点加盟商编码
    ,main.network_fran_name           as network_fran_name           --交件网点加盟商名称
    ,main.network_agent_code          as network_agent_code          --交件网点代理区编码
    ,main.network_agent_name          as network_agent_name          --交件网点代理区名称
    ,main.nodal_code                  as nodal_code                  --交件集散编码
    ,main.nodal_name                  as nodal_name                  --交件集散名称
    ,main.nodal_fran_code             as nodal_fran_code             --实际交件集散点加盟商编码
    ,main.nodal_fran_name             as nodal_fran_name             --实际交件集散点加盟商名称
    ,main.nodal_agent_code            as nodal_agent_code            --实际交件集散点代理区编码
    ,main.nodal_agent_name            as nodal_agent_name            --实际交件集散点代理区名称
    ,nvl(main.jj_cnt                    ,0)  as jj_cnt                      --应交件票数
    ,nvl(main.jj_zd_cnt                 ,0)  as jj_zd_cnt                   --准点交件票数
    ,nvl(main.jj_bzd_cnt                ,0)  as jj_bzd_cnt                  --不准点交件票数
    ,nvl(main.jj_network_cnt            ,0)  as jj_network_cnt              --交件网点责任票数
    ,nvl(main.jj_nodal_cnt              ,0)  as jj_nodal_cnt                --交件集散责任票数
    ,nvl(main.effect_qs_cnt             ,0)  as effect_qs_cnt               --时效缺失票数
    ,nvl(main.three_code_cnt            ,0)  as three_code_cnt              --三段码问题票数
    ,nvl(main.error_send_cnt            ,0)  as error_send_cnt              --错发票数
    ,nvl(main.other_cnt                 ,0)  as other_cnt                   --其他票数
    ,nvl(main.local_city_cnt            ,0)  as local_city_cnt              --同城件
    ,nvl(main.weidao_jj_cnt             ,0)  as weidao_jj_cnt               --未到交件时间票数
    ,nvl(main.route_late_cnt            ,0)  as route_late_cnt              --非最优路由票数
    ,nvl(main.center_send_timely_cnt    ,0)  as center_send_timely_cnt      --交件晚点及时发出票数
    ,nvl(main.center_send_late_cnt      ,0)  as center_send_late_cnt        --交件晚点未及时发出票数
    ,nvl(main.effect_bzd_cnt            ,0)  as effect_bzd_cnt              --时效不准点票数
    ,nvl(main.qxt_jj_cnt                ,0)  as qxt_jj_cnt                  --七星潭应交件票数
    ,nvl(main.qxt_jj_zd_cnt             ,0)  as qxt_jj_zd_cnt               --七星潭准点交件票数
    ,nvl(main.qxt_jj_bzd_cnt            ,0)  as qxt_jj_bzd_cnt              --七星潭不准点交件票数
    ,nvl(main.qxt_jj_network_cnt        ,0)  as qxt_jj_network_cnt          --七星潭交件网点责任票数
    ,nvl(main.qxt_jj_nodal_cnt          ,0)  as qxt_jj_nodal_cnt            --七星潭交件集散责任票数
    ,nvl(main.qxt_effect_qs_cnt         ,0)  as qxt_effect_qs_cnt           --七星潭时效缺失票数
    ,nvl(main.qxt_three_code_cnt        ,0)  as qxt_three_code_cnt          --七星潭三段码问题票数
    ,nvl(main.qxt_error_send_cnt        ,0)  as qxt_error_send_cnt          --七星潭错发票数
    ,nvl(main.qxt_other_cnt             ,0)  as qxt_other_cnt               --七星潭其他票数
    ,nvl(main.qxt_local_city_cnt        ,0)  as qxt_local_city_cnt          --七星潭同城件
    ,nvl(main.qxt_weidao_jj_cnt         ,0)  as qxt_weidao_jj_cnt           --七星潭未到交件时间票数
    ,nvl(main.qxt_route_late_cnt        ,0)  as qxt_route_late_cnt          --七星潭非最优路由票数
    ,nvl(main.qxt_center_send_timely_cnt,0)  as qxt_center_send_timely_cnt  --七星潭交件晚点及时发出票数
    ,nvl(main.qxt_center_send_late_cnt  ,0)  as qxt_center_send_late_cnt    --七星潭交件晚点未及时发出票数
    ,nvl(main.qxt_effect_bzd_cnt        ,0)  as qxt_effect_bzd_cnt          --七星潭时效不准点票数
    ,nvl(main.thd_jj_cnt                ,0)  as thd_jj_cnt                  --桃花岛应交件票数
    ,nvl(main.thd_jj_zd_cnt             ,0)  as thd_jj_zd_cnt               --桃花岛准点交件票数
    ,nvl(main.thd_jj_bzd_cnt            ,0)  as thd_jj_bzd_cnt              --桃花岛不准点交件票数
    ,nvl(main.thd_jj_network_cnt        ,0)  as thd_jj_network_cnt          --桃花岛交件网点责任票数
    ,nvl(main.thd_jj_nodal_cnt          ,0)  as thd_jj_nodal_cnt            --桃花岛交件集散责任票数
    ,nvl(main.thd_effect_qs_cnt         ,0)  as thd_effect_qs_cnt           --桃花岛时效缺失票数
    ,nvl(main.thd_three_code_cnt        ,0)  as thd_three_code_cnt          --桃花岛三段码问题票数
    ,nvl(main.thd_error_send_cnt        ,0)  as thd_error_send_cnt          --桃花岛错发票数
    ,nvl(main.thd_other_cnt             ,0)  as thd_other_cnt               --桃花岛其他票数
    ,nvl(main.thd_local_city_cnt        ,0)  as thd_local_city_cnt          --桃花岛同城件
    ,nvl(main.thd_weidao_jj_cnt         ,0)  as thd_weidao_jj_cnt           --桃花岛未到交件时间票数
    ,nvl(main.thd_route_late_cnt        ,0)  as thd_route_late_cnt          --桃花岛非最优路由票数
    ,nvl(main.thd_center_send_timely_cnt,0)  as thd_center_send_timely_cnt  --桃花岛交件晚点及时发出票数
    ,nvl(main.thd_center_send_late_cnt  ,0)  as thd_center_send_late_cnt    --桃花岛交件晚点未及时发出票数
    ,nvl(main.thd_effect_bzd_cnt        ,0)  as thd_effect_bzd_cnt          --桃花岛时效不准点票数
    ,nvl(main.zjs_jj_cnt                ,0)  as zjs_jj_cnt                  --紫金山应交件票数
    ,nvl(main.zjs_jj_zd_cnt             ,0)  as zjs_jj_zd_cnt               --紫金山准点交件票数
    ,nvl(main.zjs_jj_bzd_cnt            ,0)  as zjs_jj_bzd_cnt              --紫金山不准点交件票数
    ,nvl(main.zjs_jj_network_cnt        ,0)  as zjs_jj_network_cnt          --紫金山交件网点责任票数
    ,nvl(main.zjs_jj_nodal_cnt          ,0)  as zjs_jj_nodal_cnt            --紫金山交件集散责任票数
    ,nvl(main.zjs_effect_qs_cnt         ,0)  as zjs_effect_qs_cnt           --紫金山时效缺失票数
    ,nvl(main.zjs_three_code_cnt        ,0)  as zjs_three_code_cnt          --紫金山三段码问题票数
    ,nvl(main.zjs_error_send_cnt        ,0)  as zjs_error_send_cnt          --紫金山错发票数
    ,nvl(main.zjs_other_cnt             ,0)  as zjs_other_cnt               --紫金山其他票数
    ,nvl(main.zjs_local_city_cnt        ,0)  as zjs_local_city_cnt          --紫金山同城件
    ,nvl(main.zjs_weidao_jj_cnt         ,0)  as zjs_weidao_jj_cnt           --紫金山未到交件时间票数
    ,nvl(main.zjs_route_late_cnt        ,0)  as zjs_route_late_cnt          --紫金山非最优路由票数
    ,nvl(main.zjs_center_send_timely_cnt,0)  as zjs_center_send_timely_cnt  --紫金山交件晚点及时发出票数
    ,nvl(main.zjs_center_send_late_cnt  ,0)  as zjs_center_send_late_cnt    --紫金山交件晚点未及时发出票数
    ,nvl(main.zjs_effect_bzd_cnt        ,0)  as zjs_effect_bzd_cnt          --紫金山时效不准点票数
    ,nvl(main.xyf_jj_cnt                ,0)  as xyf_jj_cnt                  --逍遥峰应交件票数
    ,nvl(main.xyf_jj_zd_cnt             ,0)  as xyf_jj_zd_cnt               --逍遥峰准点交件票数
    ,nvl(main.xyf_jj_bzd_cnt            ,0)  as xyf_jj_bzd_cnt              --逍遥峰不准点交件票数
    ,nvl(main.xyf_jj_network_cnt        ,0)  as xyf_jj_network_cnt          --逍遥峰交件网点责任票数
    ,nvl(main.xyf_jj_nodal_cnt          ,0)  as xyf_jj_nodal_cnt            --逍遥峰交件集散责任票数
    ,nvl(main.xyf_effect_qs_cnt         ,0)  as xyf_effect_qs_cnt           --逍遥峰时效缺失票数
    ,nvl(main.xyf_three_code_cnt        ,0)  as xyf_three_code_cnt          --逍遥峰三段码问题票数
    ,nvl(main.xyf_error_send_cnt        ,0)  as xyf_error_send_cnt          --逍遥峰错发票数
    ,nvl(main.xyf_other_cnt             ,0)  as xyf_other_cnt               --逍遥峰其他票数
    ,nvl(main.xyf_local_city_cnt        ,0)  as xyf_local_city_cnt          --逍遥峰同城件
    ,nvl(main.xyf_weidao_jj_cnt         ,0)  as xyf_weidao_jj_cnt           --逍遥峰未到交件时间票数
    ,nvl(main.xyf_route_late_cnt        ,0)  as xyf_route_late_cnt          --逍遥峰非最优路由票数
    ,nvl(main.xyf_center_send_timely_cnt,0)  as xyf_center_send_timely_cnt  --逍遥峰交件晚点及时发出票数
    ,nvl(main.xyf_center_send_late_cnt  ,0)  as xyf_center_send_late_cnt    --逍遥峰交件晚点未及时发出票数
    ,nvl(main.xyf_effect_bzd_cnt        ,0)  as xyf_effect_bzd_cnt          --逍遥峰时效不准点票数
    ,nvl(main.jdw_jj_cnt                ,0)  as jdw_jj_cnt                  --极地湾应交件票数
    ,nvl(main.jdw_jj_zd_cnt             ,0)  as jdw_jj_zd_cnt               --极地湾准点交件票数
    ,nvl(main.jdw_jj_bzd_cnt            ,0)  as jdw_jj_bzd_cnt              --极地湾不准点交件票数
    ,nvl(main.jdw_jj_network_cnt        ,0)  as jdw_jj_network_cnt          --极地湾交件网点责任票数
    ,nvl(main.jdw_jj_nodal_cnt          ,0)  as jdw_jj_nodal_cnt            --极地湾交件集散责任票数
    ,nvl(main.jdw_effect_qs_cnt         ,0)  as jdw_effect_qs_cnt           --极地湾时效缺失票数
    ,nvl(main.jdw_three_code_cnt        ,0)  as jdw_three_code_cnt          --极地湾三段码问题票数
    ,nvl(main.jdw_error_send_cnt        ,0)  as jdw_error_send_cnt          --极地湾错发票数
    ,nvl(main.jdw_other_cnt             ,0)  as jdw_other_cnt               --极地湾其他票数
    ,nvl(main.jdw_local_city_cnt        ,0)  as jdw_local_city_cnt          --极地湾同城件
    ,nvl(main.jdw_weidao_jj_cnt         ,0)  as jdw_weidao_jj_cnt           --极地湾未到交件时间票数
    ,nvl(main.jdw_route_late_cnt        ,0)  as jdw_route_late_cnt          --极地湾非最优路由票数
    ,nvl(main.jdw_center_send_timely_cnt,0)  as jdw_center_send_timely_cnt  --极地湾交件晚点及时发出票数
    ,nvl(main.jdw_center_send_late_cnt  ,0)  as jdw_center_send_late_cnt    --极地湾交件晚点未及时发出票数
    ,nvl(main.jdw_effect_bzd_cnt        ,0)  as jdw_effect_bzd_cnt          --极地湾时效不准点票数
    ,nvl(main.qt_jj_cnt                 ,0)  as qt_jj_cnt                   --其他应交件票数
    ,nvl(main.qt_jj_zd_cnt              ,0)  as qt_jj_zd_cnt                --其他准点交件票数
    ,nvl(main.qt_jj_bzd_cnt             ,0)  as qt_jj_bzd_cnt               --其他不准点交件票数
    ,nvl(main.qt_jj_network_cnt         ,0)  as qt_jj_network_cnt           --其他交件网点责任票数
    ,nvl(main.qt_jj_nodal_cnt           ,0)  as qt_jj_nodal_cnt             --其他交件集散责任票数
    ,nvl(main.qt_effect_qs_cnt          ,0)  as qt_effect_qs_cnt            --其他时效缺失票数
    ,nvl(main.qt_three_code_cnt         ,0)  as qt_three_code_cnt           --其他三段码问题票数
    ,nvl(main.qt_error_send_cnt         ,0)  as qt_error_send_cnt           --其他错发票数
    ,nvl(main.qt_other_cnt              ,0)  as qt_other_cnt                --其他其他票数
    ,nvl(main.qt_local_city_cnt         ,0)  as qt_local_city_cnt           --其他同城件
    ,nvl(main.qt_weidao_jj_cnt          ,0)  as qt_weidao_jj_cnt            --其他未到交件时间票数
    ,nvl(main.qt_route_late_cnt         ,0)  as qt_route_late_cnt           --其他非最优路由票数
    ,nvl(main.qt_center_send_timely_cnt ,0)  as qt_center_send_timely_cnt   --其他交件晚点及时发出票数
    ,nvl(main.qt_center_send_late_cnt   ,0)  as qt_center_send_late_cnt     --其他交件晚点未及时发出票数
    ,nvl(main.qt_effect_bzd_cnt         ,0)  as qt_effect_bzd_cnt           --其他时效不准点票数
    ,case when main.dt between dim.create_date and dim.end_date then '新开网点'
          when main.dt between event.happen_time and event.end_time then '时效顺延'
          when main.dt between inter.effect_time_start and inter.effect_time_end then '筛单拦截'
          end as exempt_type  --豁免类型
    ,case when main.dt between dim.create_date and dim.end_date then null
          when main.dt between event.happen_time and event.end_time then null
          when main.dt between inter.effect_time_start and inter.effect_time_end then null
          else (main.jj_network_cnt - main.jdw_jj_network_cnt - main.qt_jj_network_cnt ) * config.fee_per end as fine_amt  --豁免类型
    ,main.dt
from (
    select
         receive_network_code    as network_code         --交件网点编码
        ,receive_network_name    as network_name         --交件网点名称
        ,receive_franchisee_code as network_fran_code    --交件网点加盟商编码
        ,receive_franchisee      as network_fran_name    --交件网点加盟商名称
        ,receive_proxy_code      as network_agent_code   --交件网点代理区编码
        ,receive_proxy           as network_agent_name   --交件网点代理区名称
        ,collect_code            as nodal_code           --交件集散编码
        ,collect_name            as nodal_name           --交件集散名称
        ,first_nodal_fran_code   as nodal_fran_code      --实际交件集散点加盟商编码
        ,first_nodal_fran_name   as nodal_fran_name      --实际交件集散点加盟商名称
        ,first_nodal_agent_code  as nodal_agent_code     --实际交件集散点代理区编码
        ,first_nodal_agent_name  as nodal_agent_name     --实际交件集散点代理区名称
        ,nvl(sum(should_hand_over_num      ),0) as jj_cnt                 --应交件票数
        ,nvl(sum(hand_over_in_time_num     ),0) as jj_zd_cnt              --准点交件票数
        ,nvl(sum(hand_over_not_in_time_num ),0) as jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(hand_over_network_resp_num),0) as jj_network_cnt         --交件网点责任票数
        ,nvl(sum(hand_over_collect_resp_num),0) as jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(plan_err_num              ),0) as effect_qs_cnt          --时效缺失票数
        ,nvl(sum(three_code_num            ),0) as three_code_cnt         --三段码问题票数
        ,nvl(sum(wrong_dispatch            ),0) as error_send_cnt         --错发票数
        ,nvl(sum(other                     ),0) as other_cnt              --其他票数
        ,nvl(sum(same_city_num             ),0) as local_city_cnt         --同城件
        ,nvl(sum(unarrival_num             ),0) as weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(route_late_cnt            ),0) as route_late_cnt         --非最优路由票数
        ,nvl(sum(center_send_timely_cnt    ),0) as center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(center_send_late_cnt      ),0) as center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(not_intime_cnt            ),0) as effect_bzd_cnt         --时效不准点票数
        --七星潭
        ,nvl(sum(case when order_source='七星潭' then should_hand_over_num       end),0) as qxt_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='七星潭' then hand_over_in_time_num      end),0) as qxt_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='七星潭' then hand_over_not_in_time_num  end),0) as qxt_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='七星潭' then hand_over_network_resp_num end),0) as qxt_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='七星潭' then hand_over_collect_resp_num end),0) as qxt_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='七星潭' then plan_err_num               end),0) as qxt_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='七星潭' then three_code_num             end),0) as qxt_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='七星潭' then wrong_dispatch             end),0) as qxt_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='七星潭' then other                      end),0) as qxt_other_cnt              --其他票数
        ,nvl(sum(case when order_source='七星潭' then same_city_num              end),0) as qxt_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='七星潭' then unarrival_num              end),0) as qxt_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='七星潭' then route_late_cnt             end),0) as qxt_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='七星潭' then center_send_timely_cnt     end),0) as qxt_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='七星潭' then center_send_late_cnt       end),0) as qxt_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='七星潭' then not_intime_cnt             end),0) as qxt_effect_bzd_cnt         --时效不准点票数
        --桃花岛
        ,nvl(sum(case when order_source='桃花岛' then should_hand_over_num       end),0) as thd_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='桃花岛' then hand_over_in_time_num      end),0) as thd_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='桃花岛' then hand_over_not_in_time_num  end),0) as thd_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='桃花岛' then hand_over_network_resp_num end),0) as thd_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='桃花岛' then hand_over_collect_resp_num end),0) as thd_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='桃花岛' then plan_err_num               end),0) as thd_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='桃花岛' then three_code_num             end),0) as thd_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='桃花岛' then wrong_dispatch             end),0) as thd_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='桃花岛' then other                      end),0) as thd_other_cnt              --其他票数
        ,nvl(sum(case when order_source='桃花岛' then same_city_num              end),0) as thd_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='桃花岛' then unarrival_num              end),0) as thd_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='桃花岛' then route_late_cnt             end),0) as thd_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='桃花岛' then center_send_timely_cnt     end),0) as thd_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='桃花岛' then center_send_late_cnt       end),0) as thd_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='桃花岛' then not_intime_cnt             end),0) as thd_effect_bzd_cnt         --时效不准点票数
        --紫金山
        ,nvl(sum(case when order_source='紫金山' then should_hand_over_num       end),0) as zjs_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='紫金山' then hand_over_in_time_num      end),0) as zjs_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='紫金山' then hand_over_not_in_time_num  end),0) as zjs_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='紫金山' then hand_over_network_resp_num end),0) as zjs_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='紫金山' then hand_over_collect_resp_num end),0) as zjs_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='紫金山' then plan_err_num               end),0) as zjs_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='紫金山' then three_code_num             end),0) as zjs_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='紫金山' then wrong_dispatch             end),0) as zjs_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='紫金山' then other                      end),0) as zjs_other_cnt              --其他票数
        ,nvl(sum(case when order_source='紫金山' then same_city_num              end),0) as zjs_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='紫金山' then unarrival_num              end),0) as zjs_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='紫金山' then route_late_cnt             end),0) as zjs_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='紫金山' then center_send_timely_cnt     end),0) as zjs_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='紫金山' then center_send_late_cnt       end),0) as zjs_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='紫金山' then not_intime_cnt             end),0) as zjs_effect_bzd_cnt         --时效不准点票数
        --逍遥峰
        ,nvl(sum(case when order_source='逍遥峰' then should_hand_over_num       end),0) as xyf_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='逍遥峰' then hand_over_in_time_num      end),0) as xyf_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='逍遥峰' then hand_over_not_in_time_num  end),0) as xyf_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='逍遥峰' then hand_over_network_resp_num end),0) as xyf_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='逍遥峰' then hand_over_collect_resp_num end),0) as xyf_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='逍遥峰' then plan_err_num               end),0) as xyf_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='逍遥峰' then three_code_num             end),0) as xyf_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='逍遥峰' then wrong_dispatch             end),0) as xyf_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='逍遥峰' then other                      end),0) as xyf_other_cnt              --其他票数
        ,nvl(sum(case when order_source='逍遥峰' then same_city_num              end),0) as xyf_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='逍遥峰' then unarrival_num              end),0) as xyf_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='逍遥峰' then route_late_cnt             end),0) as xyf_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='逍遥峰' then center_send_timely_cnt     end),0) as xyf_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='逍遥峰' then center_send_late_cnt       end),0) as xyf_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='逍遥峰' then not_intime_cnt             end),0) as xyf_effect_bzd_cnt         --时效不准点票数
        --极地湾
        ,nvl(sum(case when order_source='极地湾' then should_hand_over_num       end),0) as jdw_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source='极地湾' then hand_over_in_time_num      end),0) as jdw_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source='极地湾' then hand_over_not_in_time_num  end),0) as jdw_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source='极地湾' then hand_over_network_resp_num end),0) as jdw_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source='极地湾' then hand_over_collect_resp_num end),0) as jdw_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source='极地湾' then plan_err_num               end),0) as jdw_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source='极地湾' then three_code_num             end),0) as jdw_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source='极地湾' then wrong_dispatch             end),0) as jdw_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source='极地湾' then other                      end),0) as jdw_other_cnt              --其他票数
        ,nvl(sum(case when order_source='极地湾' then same_city_num              end),0) as jdw_local_city_cnt         --同城件
        ,nvl(sum(case when order_source='极地湾' then unarrival_num              end),0) as jdw_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source='极地湾' then route_late_cnt             end),0) as jdw_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source='极地湾' then center_send_timely_cnt     end),0) as jdw_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source='极地湾' then center_send_late_cnt       end),0) as jdw_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source='极地湾' then not_intime_cnt             end),0) as jdw_effect_bzd_cnt         --时效不准点票数
        --其他
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then should_hand_over_num       end),0) as qt_jj_cnt                 --应交件票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then hand_over_in_time_num      end),0) as qt_jj_zd_cnt              --准点交件票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then hand_over_not_in_time_num  end),0) as qt_jj_bzd_cnt             --不准点交件票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then hand_over_network_resp_num end),0) as qt_jj_network_cnt         --交件网点责任票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then hand_over_collect_resp_num end),0) as qt_jj_nodal_cnt           --交件集散责任票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then plan_err_num               end),0) as qt_effect_qs_cnt          --时效缺失票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then three_code_num             end),0) as qt_three_code_cnt         --三段码问题票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then wrong_dispatch             end),0) as qt_error_send_cnt         --错发票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then other                      end),0) as qt_other_cnt              --其他票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then same_city_num              end),0) as qt_local_city_cnt         --同城件
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then unarrival_num              end),0) as qt_weidao_jj_cnt          --未到交件时间票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then route_late_cnt             end),0) as qt_route_late_cnt         --非最优路由票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then center_send_timely_cnt     end),0) as qt_center_send_timely_cnt --交件晚点及时发出票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then center_send_late_cnt       end),0) as qt_center_send_late_cnt   --交件晚点未及时发出票数
        ,nvl(sum(case when order_source not in ('七星潭','桃花岛','逍遥峰','紫金山','极地湾') then not_intime_cnt             end),0) as qt_effect_bzd_cnt         --时效不准点票数
        ,'交件准点率汇总' as fee_menu
        ,dt
    from jms_dm.dm_waybill_collect_intime_taking_agg
    where dt = date_sub('{{ execution_date | cst_ds }}',1)
    group by receive_network_code      --收件网点编码
            ,receive_network_name      --收件网点名称
            ,receive_franchisee_code   --收件网点加盟商编码
            ,receive_franchisee        --收件网点加盟商名称
            ,receive_proxy_code        --收件网点代理区编码
            ,receive_proxy             --收件网点代理区名称
            ,collect_code              --交件集散编码
            ,collect_name              --交件集散名称
            ,first_nodal_fran_code     --实际交件集散点加盟商编码
            ,first_nodal_fran_name     --实际交件集散点加盟商名称
            ,first_nodal_agent_code    --实际交件集散点代理区编码
            ,first_nodal_agent_name    --实际交件集散点代理区名称
            ,dt
) main
left join (
    select
    fee_per,fee_menu
    from jms_dim.dim_tab_forfeit_config_new
    where statue = 1
    and is_delete = 1
    and date_sub('{{ execution_date | cst_ds }}',1) between fee_start_time and fee_end_time
    and fee_menu = '交件准点率汇总'
) config on config.fee_menu = main.fee_menu
left join (
    select
         code
        ,name
        ,create_time
        ,to_date(opening_time) as create_date
        ,date_add(to_date(opening_time),15)  as end_date
    from jms_dim.dim_network_whole_massage  --网点资料，豁免开往+15天
) dim on main.network_code = dim.code
left join (
    select
         involved_org_code as involved_org_code --涉事组织编码
        ,involved_org_name as involved_org_name --涉事组织名称
        ,max(report_time)  as report_time       --上报时间
        ,to_date(max(happen_time)) as happen_time  --发生时间
        ,to_date(max(end_time   )) as end_time     --结束时间
    from jms_dim.dim_abnormal_event_report_base --异常事件报表
    -- where to_date(happen_time) between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
    where to_date(happen_time) <= date_sub('{{ execution_date | cst_ds }}',1)
      and to_date(end_time)    >= date_sub('{{ execution_date | cst_ds }}',1)
    group by involved_org_code   --涉事组织编码
            ,involved_org_name   --涉事组织名称
) event on main.network_code = event.involved_org_code
left join (
    select
         network_code       --网点编码
        ,network_name       --网点名称
        ,to_date(effect_time_start) as effect_time_start --开始时间
        ,date_add(to_date(effect_time_end),5) as effect_time_end  --结束时间
    from jms_dm.dm_waybill_collect_intime_interceptor_base
) inter on inter.network_code = main.network_code
distribute by 1;